package com.qst.dao;

import com.qst.Db;
import com.qst.ExamException;
import com.qst.entity.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao {

    //通过登录名获取用户对象
    public User findByLogin(String login) {
        String sql = "select * from users where login = ?";
        //jdbc语法糖
        try (Connection connection = Db.getConnection();
             PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setObject(1, login);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return createUser(rs);
            }
            return null;
        } catch (SQLException e) {
            throw new ExamException(sql, e);
        }
    }

    //通过id获取用户对象
    public User findById(int id) {
        String sql = "select * from users where id = ?";
        //jdbc语法糖
        try (Connection connection = Db.getConnection();
             PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setObject(1, id);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return createUser(rs);
            }
            return null;
        } catch (SQLException e) {
            throw new ExamException(sql, e);
        }
    }

    //通过id修改用户最后登录时间
    public void updateLastLogin(Integer userId, Timestamp lastLogin) {
        String sql = "update users set last_login = ? where id = ?";
        //jdbc语法糖
        try (Connection connection = Db.getConnection();
             PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setObject(1, lastLogin);
            stmt.setObject(2, userId);
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new ExamException(sql, e);
        }
    }

    public User createUser(ResultSet rs) throws SQLException {
        return new User(rs.getInt(1), rs.getString(2), rs.getString(3),
                rs.getString(4), rs.getInt(5), rs.getInt(6));
    }

    //查询全部
    public List<User> findAll() {
        String sql = "select * from users where `type` < 4";
        List<User> users = new ArrayList<>();
        //jdbc语法糖
        try (Connection connection = Db.getConnection();
             PreparedStatement stmt = connection.prepareStatement(sql)) {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                users.add(createUser(rs));
            }
        } catch (SQLException e) {
            throw new ExamException(sql, e);
        }
        return users;
    }

    public void deleteById(int id) {
        String sql = "delete from users where id=?";
        //jdbc语法糖
        try (Connection conn = Db.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setObject(1, id);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            throw new ExamException(sql, ex);
        }
    }

    public int insert(User u) {
        String sql = "insert into users(login,passwd,name,type,status) values(?,?,?,?,?)";
        try (Connection conn = Db.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql,
                     Statement.RETURN_GENERATED_KEYS)) {
            stmt.setObject(1, u.getLogin());
            stmt.setObject(2, u.getPasswd());
            stmt.setObject(3, u.getName());
            stmt.setObject(4, u.getType());
            stmt.setObject(5, u.getStatus());
            stmt.executeUpdate();
            u.setId(Db.getGeneratedInt(stmt));
            return u.getId();
        } catch (SQLException ex) {
            throw new ExamException(sql, ex);
        }
    }

    public void update(User u) {
        String sql = "update users set login=?,name=?,type=?,status=? where id=?";
        try (Connection conn = Db.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setObject(1, u.getLogin());
            stmt.setObject(2, u.getName());
            stmt.setObject(3, u.getType());
            stmt.setObject(4, u.getStatus());
            stmt.setObject(5, u.getId());
            stmt.executeUpdate();
        } catch (SQLException ex) {
            throw new ExamException(sql, ex);
        }
    }

    public void updatePassword(int id, String password) {
        String sql = "update users set passwd=? where id=?";
        try (Connection conn = Db.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setObject(1, password);
            stmt.setObject(2, id);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            throw new ExamException(sql, ex);
        }
    }

}
